Limitations and Considerations

The following limitations apply when using Google Cloud BigQuery as a data warehouse in a Compose project.

• The dataset(s) specified in the connection settings must already exist before loading data into Google Cloud BigQuery.

• When ingesting data from a Replicate source that may have BIT columns (such as Microsoft SQL Server), it is recommended to define a global data type transformation in Replicate to convert BIT to STRING (1). Otherwise, Compose will convert BIT to VARCHAR (1) (as it does not support BOOLEAN), which may cause a data type mismatch in the Landing Zone.

• When discovering from a BigQuery landing database, BOOLEAN and FLOAT columns are not supported and will be ignored. If you need such columns to be ingested to the data warehouse, the following workarounds are available:
  • Discover from the source database
  • Convert these data types (which are not supported in Compose) to another type such as VARCHAR (1) or INT
• The data warehouse data set and landing data set must be in the same region.
• As strings do not have length in BigQuery, when discovering from the Landing Zone, Compose will assume a default length of VARCHAR(32767). From a practical perspective, since these strings will also be created on BigQuery, they will have no runtime length either. To keep things orderly however, best practice is to change strings of known length to their actual expected length.
• Commonly used BigQuery functions were added to the Compose Expression Builder. BigQuery SQL commands that are not listed in the Compose Expression Builder can be entered manually if required.
• BigQuery does not support altering tables via standard DDL operations. To work around this limitation, Compose creates a script that copies the data to a new table. After the data is copied to the new table, make sure to delete the old table.
• Aggregated fact and state oriented data mart are not supported.
• Stored procedures in custom ETLs are not supported.
• Clustering keys are not supported.